package tp.tecnicasdisenio.dao.impl.jdbc;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;

import tp.tecnicasdisenio.dao.DaoException;
import tp.tecnicasdisenio.dao.TicketDaoInterface;
import tp.tecnicasdisenio.model.entity.Ticket;

public class TicketDao extends DaoAbstract implements TicketDaoInterface{

	private String nombreTabla = "tickets", atributoId = "ticket_id";
	
	private static TicketDao instancia = null;

	private TicketDao() {}
	
	public static TicketDao getInstance() {
		if (instancia == null)
			instancia = new TicketDao();
		return instancia;
	}
	
	
	private ArrayList<Ticket> obtenerTicket(Integer id, boolean unoSolo) {
		ArrayList<Ticket> resultado = null;
		
		try {
			String queryString = "SELECT * FROM " + this.nombreTabla;
			
			if (unoSolo)
				queryString = queryString.concat(" WHERE " + this.atributoId + "=?");

			connection = this.getConnection();
			preparedStatement = connection.prepareStatement(queryString);
			
			if (unoSolo)
				preparedStatement.setInt(1, id);
			
			resultSet = preparedStatement.executeQuery();
			
			resultado = new ArrayList<Ticket>();
			Ticket ticketDB = null;
			
			while (resultSet.next()) {
				ticketDB = new Ticket();
				ticketDB.setTicketId(resultSet.getInt("ticket_id"));
				ticketDB.setTema(resultSet.getString("tema"));
				ticketDB.setPadron(( (Integer)resultSet.getInt("padron") ).toString());
				ticketDB.setMimeId(resultSet.getInt("mime_id"));
				ticketDB.setFecha(resultSet.getTimestamp("fecha"));
				
				resultado.add(ticketDB);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw (new DaoException(e));
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return resultado;
	}
	
	@Override
	public Ticket getPorId(Integer id) {
		ArrayList<Ticket> listaTickets = this.obtenerTicket(id, true);
		
		Ticket resultado = null;
		if (!listaTickets.isEmpty())
			resultado = listaTickets.iterator().next();
		
		return resultado;
	}

	@Override
	public ArrayList<Ticket> obtenerTodos() {
		return (this.obtenerTicket(0, false));
	}

	@Override
	public void borrar(Ticket entidad) {
		try {
			String queryString = "DELETE FROM " + this.nombreTabla + " WHERE " + this.atributoId + "=?";
			connection = getConnection();
			preparedStatement = connection.prepareStatement(queryString);
			preparedStatement.setInt(1, entidad.getTicketId());
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw (new DaoException(e));
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public void actualizar(Ticket entidad) {
		try {
			String queryString = "UPDATE " + this.nombreTabla + " SET tema=?, padron=?, mime_id=?, fecha=? WHERE " + this.atributoId + "=?";
			connection = getConnection();
			preparedStatement = connection.prepareStatement(queryString);
			preparedStatement.setString(1, entidad.getTema());
			preparedStatement.setInt(2, (new Integer(entidad.getPadron())));
			preparedStatement.setInt(3, entidad.getMimeId());
			preparedStatement.setTimestamp(4, (new Timestamp(entidad.getFecha().getTime())));
			preparedStatement.setInt(5, entidad.getTicketId());
			
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw (new DaoException(e));
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}		
	}

	@Override
	public void insertar(Ticket entidad) {
		try {
			String queryString = "INSERT INTO " + this.nombreTabla + "(tema, padron, mime_id, fecha) VALUES(?,?,?,?)";
			connection = getConnection();
			preparedStatement = connection.prepareStatement(queryString);
			preparedStatement.setString(1, entidad.getTema());
			preparedStatement.setString(2, entidad.getPadron());
			preparedStatement.setInt(3, entidad.getMimeId());
			preparedStatement.setTimestamp(4, (new Timestamp(entidad.getFecha().getTime())));
			
			preparedStatement.executeUpdate();
			
			// luego de insertarlo en la bd, seteo el valor de ticket generado en la entidad
			preparedStatement = connection.prepareStatement("SELECT MAX(ticket_id) FROM tickets");
			resultSet = preparedStatement.executeQuery();
			resultSet.first();
			entidad.setTicketId(resultSet.getInt("MAX(ticket_id)"));
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw (new DaoException(e));
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public Ticket obtenerUltimoTicketDelTema(String tema) {
		Ticket ticket = null;
		try {
			String queryString = "SELECT * FROM " + this.nombreTabla + " WHERE tema=? ORDER BY ticket_id DESC LIMIT 1";
			connection = getConnection();
			preparedStatement = connection.prepareStatement(queryString);
			preparedStatement.setString(1, tema);

			resultSet = preparedStatement.executeQuery();
			
			if (resultSet.next()) {
				ticket = new Ticket();
				ticket.setTema(resultSet.getString("tema"));
				ticket.setTicketId(resultSet.getInt("ticket_id"));
				ticket.setMimeId(resultSet.getInt("mime_id"));
				ticket.setPadron(((Integer)resultSet.getInt("padron")).toString());
				ticket.setFecha(new Date(resultSet.getTimestamp("fecha").getTime()));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw (new DaoException(e));
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return ticket;
	}
}
